/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.Version;
import service.Cartao;

/**
 *
 * @author helanio
 */
public class PostgresDAO {
    private static String dbUrl = "jdbc:postgresql://localhost:5432/soa";
    private static Connection conn = null;
    
    private static void abrirConexao(){
        try{
            Class.forName("org.postgresql.Driver").newInstance();
            conn = DriverManager.getConnection(dbUrl, "postgres", "postgres");
        } catch (Exception e){
            e.printStackTrace();
        }
    }
    
    private static void fecharConexao(ResultSet rs, PreparedStatement stmt){
        try {
            if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
        } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Version.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
        }
        
    }
    
    
    public static Cartao consultaCartao (long cpf){
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Cartao cartao = null;
        try {
            abrirConexao();
            String sql = "select * from cartao_de_credito where cpf = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, cpf);
            cartao = new Cartao();
            rs = stmt.executeQuery();
            while (rs.next()){
                cartao.setCodigo(rs.getString("codigo_do_cartao"));
                cartao.setCpf(rs.getLong("cpf"));
                cartao.setNomeScritoNoCartao(rs.getString("nome_escrito_no_cartao"));
                cartao.setNumeroCartao(rs.getLong("numero_do_cartao"));
                cartao.setValidade(rs.getDate("validade").toString());
                cartao.setCredito(rs.getDouble("credito_disponivel"));
               
            }
        
        } catch (SQLException e){
            e.printStackTrace();
            
        } finally{
             fecharConexao(rs, stmt);
        }
        return cartao;
    }
    
    public static void registroPagamento(Date dataOperacao, long nCartao, String codigoCartao, double valor){
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Cartao cartao = null;
        try {
            abrirConexao();
            String sql = "insert into registro_de_pagamento (data_da_operacao, numero_do_cartao, codigo_do_cartao, valor) values (?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setDate(1, dataOperacao);
            stmt.setLong(2, nCartao);
            stmt.setString(3, codigoCartao);
            stmt.setDouble(4, valor);
            stmt.execute();
        } catch (Exception e){
            e.printStackTrace();
            
        } finally{
             fecharConexao(rs, stmt);
        }
    }
    
    public static String[] consultaUltimoPagamentoRegistrado(){
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String pagamentoRegistrado [] = new String[5];
        try {
            abrirConexao();
            String sql = "select * from registro_de_pagamento where nota_fiscal = (select max(nota_fiscal) from registro_de_pagamento);";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()){
                pagamentoRegistrado[0] = Long.toString(rs.getLong("nota_fiscal"));
                pagamentoRegistrado[1] = rs.getDate("data_da_operacao").toString();
                pagamentoRegistrado[2] = Long.toString(rs.getLong("numero_do_cartao"));
                pagamentoRegistrado[3] = rs.getString("codigo_do_cartao");
                pagamentoRegistrado[4] = Double.toString(rs.getDouble("valor"));
            }
        
        } catch (SQLException e){
            e.printStackTrace();
            
        } finally{
             fecharConexao(rs, stmt);
        }
        return pagamentoRegistrado;
    }
    
    public static long obterUltimaNotafiscal(){
        PreparedStatement stmt = null;
        ResultSet rs = null;
        long notaFiscal = 0;
        try {
            abrirConexao();
            String sql = "select nota_fiscal from registro_de_pagamento where nota_fiscal = (select max(nota_fiscal) from registro_de_pagamento);";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()){
                notaFiscal = rs.getLong("nota_fiscal");
            }
        
        } catch (SQLException e){
            e.printStackTrace();
            
        } finally{
             fecharConexao(rs, stmt);
        }
        return notaFiscal;
    
    }
    
    

}
